package com.dy.dao;

import com.dy.baen.NewOrOld;
import com.dy.baen.Sugar;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b.`name`,sum(final_total_amount)'value' FROM  order_info o \n" +
            "LEFT JOIN base_province b on province_id = b.id \n" +
            "GROUP BY b.`name`")
    List<Sugar> showMap();
    @Select("SELECT b.`name`,sum(final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id  \n" +
            "LEFT join base_trademark b on s.tm_id = b.id \n" +
            "GROUP BY b.`name`")
    List<Sugar> showPp();
    @Select("SELECT b1.`name`,sum(final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id  \n" +
            "LEFT JOIN base_category3 b3 on s.category3_id = b3.id\n" +
            "LEFT JOIN base_category2 b2 on b3.category2_id=b2.id\n" +
            "LEFT JOIN base_category1 b1 on b2.category1_id=b1.id\n" +
            "GROUP BY b1.`name`")
    List<Sugar> showZb();
    @Select("SELECT a.newUser'newUser',b.oldUser'oldUser' FROM\n" +
            "(SELECT count(*)'newUser' FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())<1) a,\n" +
            "(SELECT count(*)'oldUser' FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())>1) b")
    List<NewOrOld> showUser();
}
